package com.example.studytest.Repository.OpenData;


import com.example.studytest.Entity.OpenData.OpenData;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;

public interface OpenDataRepository extends JpaRepository<OpenData,Integer> {
    @Query(value = "SELECT a.id, b.exp_date, b.exp_name, a.invited_code, b.exp_session, b.exp_type, b.test_session, b.trial_index, b.stimulus, b.user_response, b.user_response_stimulus, b.button_pressed, b.rt, b.time_elapsed, b.correct, b.total_score " +
            "FROM t_user a inner JOIN aural_vl as b ON a.email = b.part_email " +
            "WHERE a.email=:email ", nativeQuery = true)
    List<Map<String, Object>> findAVLByEmail(@Param("email") String email);
//    List<Map<String, Object>> findAVLByEmail(@Param("email") String email);

    @Query(value = "SELECT a.id, b.exp_date, b.exp_name, a.invited_code, b.exp_session, b.exp_type, b.test_session, b.trial_index, b.stimulus, b.rt, b.time_elapsed, b.correct, b.total_score " +
            "FROM t_user a inner JOIN aural_sd as b ON a.email = b.part_email " +
            "WHERE a.email=:email ", nativeQuery = true)
    List<Map<String, Object>> findASDByEmail(@Param("email") String email);

    @Query(value = "SELECT a.id, b.exp_date, b.exp_name, a.invited_code, b.exp_session, b.exp_type, b.test_session, b.trial_index, b.stimulus, b.user_response, b.user_response_stimulus, b.button_pressed, b.rt, b .time_elapsed, b.correct, b.total_score " +
            "FROM t_user a inner JOIN aural_laa as b ON a.email = b.part_email " +
            "WHERE a.email =:email", nativeQuery = true)
    List<Map<String, Object>> findALAAByEmail(@Param("email") String email);

    @Query(value = "SELECT a.id, b.exp_date, a.invited_code, b.exp_session, b.exp_type, b.trial_type, b.trial_index, b.number,b.user_answer, b.rt, c.letter_score, c.sentence_score\n" +
            "FROM t_user a inner JOIN aural_wm b ON a.email = b.part_email INNER JOIN aural_score c on a.email = c.email " +
            "WHERE a.email=:email",nativeQuery = true)
    List<Map<String, Object>> findAWMByEmail(@Param("email") String email);

    @Query(value = "SELECT a.id, b.exp_date, b.exp_name, a.invited_code, b.exp_session, b.exp_type, b.test_session, b.trial_index, b.stimulus, b.user_response, b.user_response_stimulus, b.button_pressed, b.rt, b.time_elapsed, b.correct, b.total_score " +
            "FROM t_user a inner JOIN written_vl as b ON a.email = b.part_email " +
            "WHERE a.email=:email ", nativeQuery = true)
    List<Map<String, Object>> findWVLByEmail(@Param("email") String email);

    @Query(value = "SELECT a.id, b.exp_date, b.exp_name, a.invited_code, b.exp_session, b.exp_type, b.test_session, b.trial_index, b.stimulus, b.rt, b.time_elapsed, b.correct, b.total_score " +
            "FROM t_user a inner JOIN word_sd as b ON a.email = b.part_email " +
            "WHERE a.email=:email ", nativeQuery = true)
    List<Map<String, Object>> findWSDByEmail(@Param("email") String email);

    @Query(value = "SELECT a.id, b.exp_date, b.exp_name, a.invited_code, b.exp_session, b.exp_type, b.test_session, b.trial_index, b.stimulus, b.user_response, b.user_response_stimulus, b.button_pressed, b.rt, b .time_elapsed, b.correct, b.total_score " +
            "FROM t_user a inner JOIN written_laa as b ON a.email = b.part_email " +
            "WHERE a.email =:email", nativeQuery = true)
    List<Map<String, Object>> findWLAAByEmail(@Param("email") String email);

    @Query(value = "SELECT a.id, b.exp_date, a.invited_code, b.exp_session, b.exp_type, b.trial_type, b.trial_index, b.number,b.user_answer, b.rt, c.letter_score, c.sentence_score\n" +
            "FROM t_user a inner JOIN written_wm b ON a.email = b.part_email INNER JOIN word_score c on a.email = c.email " +
            "WHERE a.email=:email",nativeQuery = true)
    List<Map<String, Object>> findWWMByEmail(@Param("email") String email);












}
